/**
 * 
 */
package com.aspire.android.heliar.pub.storage;

import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.aspire.android.heliar.Heliar;
import com.aspire.android.heliar.pub.ContentNode;
import com.aspire.android.heliar.pub.ImageAttachment;
import com.aspire.android.heliar.pub.Pub;
import com.aspire.android.heliar.pub.PubAttachment;
import com.aspire.android.heliar.pub.SoundAttachment;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author panglei
 * 
 */
public class SQLitePubStorageManager extends SQLiteOpenHelper implements
		PubStorageManager {

	private static final int DB_VERSION = 1;

	private static final String DB_NAME = "heliar.db3";

	private static final String CREATE_TABLE_ATTACHMENT = "CREATE TABLE Attachment (contentNodeId NUMERIC, _id INTEGER PRIMARY KEY, cid , type TEXT, filepath TEXT, pubId NUMERIC);";
	private static final String CREATE_TABLE_CONTENT_NODE = "CREATE TABLE ContentNode (pubId NUMERIC, _id INTEGER PRIMARY KEY, parentId NUMERIC, title TEXT, content BLOB, author );";
	private static final String CREATE_TABLE_PUB = "CREATE TABLE Pub (key TEXT, coverId NUMERIC, _id INTEGER PRIMARY KEY, title TEXT, date NUMERIC, author TEXT, desc TEXT, rawId TEXT, rawFullContent BLOB, type NUMERIC);";

	/*
	 * private static final String DROP_TABLE_PUB = "DROP TABLE IF EXISTS Pub";
	 * private static final String DROP_TABLE_CONTENT_NODE =
	 * "DROP TABLE IF EXISTS ContentNode"; private static final String
	 * DROP_TABLE_ATTACMENT = "DROP TABLE IF EXISTS Pub";
	 */
	public static String TABLE_NAME_PUB = "Pub";
	public static String TABLE_NAME_CONTENT_NODE = "ContentNode";
	public static String TABLE_NAME_ATTACHMENT = "Attachment";

	public static String FIELD_ID = "_id";

	public static String FIELD_PUB_COVER_ID = "coverId";
	public static String FIELD_PUB_TITLE = "title";
	public static String FIELD_PUB_DATE = "date";
	public static String FIELD_PUB_AUTHOR = "author";
	public static String FIELD_PUB_DESC = "desc";
	public static String FIELD_PUB_RAW_ID = "rawId";
	public static String FIELD_PUB_RAW_FULL_CONTENT = "rawFullContent";
	public static String FIELD_PUB_TYPE = "type";
	public static String FIELD_PUB_KEY = "key";

	public static String FIELD_CONTENT_NODE_PUB_ID = "pubId";
	public static String FIELD_CONTENT_NODE_PARENT_ID = "parentId";
	public static String FIELD_CONTENT_NODE_TITLE = "title";
	public static String FIELD_CONTENT_NODE_CONTENT = "content";
	public static String FIELD_CONTENT_NODE_AUTHOR = "author";

	public static String FIELD_ATTACHMENT_CONTENT_NODE_ID = "contentNodeId";
	public static String FIELD_ATTACHMENT_CID = "cid";
	public static String FIELD_ATTACHMENT_TYPE = "type";
	public static String FIELD_ATTACHMENT_FILEPATH = "filepath";
	public static String FIELD_ATTACHMENT_PUB_ID = "pubId";

	public SQLitePubStorageManager(Context context) {

		super(context, DB_NAME, null, DB_VERSION);
		Log.d(Heliar.LOG_TAG, "Init SQLitePubStorageManager context = "
				+ context);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(Heliar.LOG_TAG, "Create Heliar Table");
		db.execSQL(CREATE_TABLE_PUB);
		db.execSQL(CREATE_TABLE_CONTENT_NODE);
		db.execSQL(CREATE_TABLE_ATTACHMENT);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i(Heliar.LOG_TAG, "Upgrade Heliar Table");
	}

	private ContentValues createPubContentValues(Pub pub) {
		ContentValues cv = new ContentValues();

		cv.put(FIELD_PUB_AUTHOR, pub.getAuthor());
		cv.put(FIELD_PUB_DATE, pub.getDate().getTime());
		cv.put(FIELD_PUB_TITLE, pub.getTitle());
		cv.put(FIELD_PUB_DESC, pub.getDesc());
		cv.put(FIELD_PUB_RAW_ID, pub.getRawId());
		cv.put(FIELD_PUB_RAW_FULL_CONTENT, pub.getRawFullContent());
		cv.put(FIELD_PUB_TYPE, pub.getType());
		cv.put(FIELD_PUB_KEY, pub.getKey());
		cv.put(FIELD_PUB_COVER_ID, pub.getCoverId());
		return cv;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#insertPub(com
	 * .aspire.android.heliar.pub.Pub)
	 */
	@Override
	public long insertPub(Pub pub) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = createPubContentValues(pub);
		long id = db.insert(TABLE_NAME_PUB, null, cv);
		db.close();
		return id;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#updatePub(int,
	 * com.aspire.android.heliar.pub.Pub)
	 */
	@Override
	public void updatePub(int id, Pub pub) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Integer.toString(id) };
		ContentValues cv = createPubContentValues(pub);
		db.update(TABLE_NAME_PUB, cv, where, whereValue);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#deletePub(int)
	 */
	@Override
	public void deletePub(long id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Long.toString(id) };
		db.delete(TABLE_NAME_PUB, where, whereValue);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#listPub()
	 */
	@Override
	public List<Pub> listPubs() {
		List<Pub> pubs = new LinkedList<Pub>();
		SQLiteDatabase db = this.getReadableDatabase();
		String[] columns = new String[] { FIELD_ID, FIELD_PUB_COVER_ID,
				FIELD_PUB_TITLE, FIELD_PUB_DATE, FIELD_PUB_AUTHOR,
				FIELD_PUB_DESC, FIELD_PUB_RAW_ID, FIELD_PUB_RAW_FULL_CONTENT,
				FIELD_PUB_TYPE, FIELD_PUB_KEY };
		Cursor c = db.query(TABLE_NAME_PUB, columns, null, null, null, null,
				FIELD_PUB_DATE + " DESC");
		if (c.moveToFirst()) {
			do {
				Pub pub = new Pub();
				pub.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
				pub.setAuthor(c.getString(c.getColumnIndex(FIELD_PUB_AUTHOR)));
				pub.setDesc(c.getString(c.getColumnIndex(FIELD_PUB_DESC)));
				pub.setKey(c.getString(c.getColumnIndex(FIELD_PUB_KEY)));
				pub.setRawId(c.getString(c.getColumnIndex(FIELD_PUB_RAW_ID)));
				pub.setTitle(c.getString(c.getColumnIndex(FIELD_PUB_TITLE)));
				pub.setType(c.getString(c.getColumnIndex(FIELD_PUB_TYPE)));
				pub.setRawFullContent(c.getString(c
						.getColumnIndex(FIELD_PUB_RAW_FULL_CONTENT)));
				pub.setDate(new Date(c.getInt(c.getColumnIndex(FIELD_PUB_DATE))));
				pub.setCoverId(c.getLong(c.getColumnIndex(FIELD_PUB_COVER_ID)));

				pubs.add(pub);
			} while (c.moveToNext());
		}
		db.close();
		return pubs;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#getPub(int)
	 */
	@Override
	public Pub getPub(long pubId) {
		Pub pub = null;
		SQLiteDatabase db = this.getReadableDatabase();
		String[] columns = new String[] { FIELD_ID, FIELD_PUB_COVER_ID,
				FIELD_PUB_TITLE, FIELD_PUB_DATE, FIELD_PUB_AUTHOR,
				FIELD_PUB_DESC, FIELD_PUB_RAW_ID, FIELD_PUB_RAW_FULL_CONTENT,
				FIELD_PUB_TYPE, FIELD_PUB_KEY };
		Cursor c = db.query(TABLE_NAME_PUB, columns, FIELD_ID + "=?",
				new String[] { String.valueOf(pubId) }, null, null, null);
		if (c.moveToFirst()) {
			pub = new Pub();
			pub.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
			pub.setAuthor(c.getString(c.getColumnIndex(FIELD_PUB_AUTHOR)));
			pub.setDesc(c.getString(c.getColumnIndex(FIELD_PUB_DESC)));
			pub.setKey(c.getString(c.getColumnIndex(FIELD_PUB_KEY)));
			pub.setRawId(c.getString(c.getColumnIndex(FIELD_PUB_RAW_ID)));
			pub.setTitle(c.getString(c.getColumnIndex(FIELD_PUB_TITLE)));
			pub.setType(c.getString(c.getColumnIndex(FIELD_PUB_TYPE)));
			pub.setRawFullContent(c.getString(c
					.getColumnIndex(FIELD_PUB_RAW_FULL_CONTENT)));
			pub.setDate(new Date(c.getInt(c.getColumnIndex(FIELD_PUB_DATE))));
			pub.setCoverId(c.getLong(c.getColumnIndex(FIELD_PUB_COVER_ID)));
		}
		db.close();
		return pub;
	}

	private ContentValues createContentNodeContentValues(ContentNode contentNode) {
		ContentValues cv = new ContentValues();
		cv.put(FIELD_CONTENT_NODE_PUB_ID, contentNode.getPubId());
		cv.put(FIELD_CONTENT_NODE_PARENT_ID, contentNode.getParentId());
		cv.put(FIELD_CONTENT_NODE_TITLE, contentNode.getTitle());
		cv.put(FIELD_CONTENT_NODE_CONTENT, contentNode.getContent());
		cv.put(FIELD_CONTENT_NODE_AUTHOR, contentNode.getAuthor());
		return cv;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#insertContent
	 * (com.aspire.android.heliar.pub.ContentNode)
	 */
	@Override
	public long insertContentNode(ContentNode contentNode) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = createContentNodeContentValues(contentNode);
		long id = db.insert(TABLE_NAME_CONTENT_NODE, null, cv);
		db.close();
		return id;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#updateContent
	 * (int, com.aspire.android.heliar.pub.ContentNode)
	 */
	@Override
	public void updateContentNode(long id, ContentNode contentNode) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Long.toString(id) };
		ContentValues cv = createContentNodeContentValues(contentNode);
		db.update(TABLE_NAME_CONTENT_NODE, cv, where, whereValue);
		db.close();

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#deleteContent
	 * (int)
	 */
	@Override
	public void deleteContentNode(long id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Long.toString(id) };
		db.delete(TABLE_NAME_CONTENT_NODE, where, whereValue);
		db.close();

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#list(int,
	 * int)
	 */
	@Override
	public List<ContentNode> listContentNodes(long pubId, long parentId) {
		List<ContentNode> contentNodes = new LinkedList<ContentNode>();
		String[] columns = new String[] { FIELD_ID, FIELD_CONTENT_NODE_PUB_ID,
				FIELD_CONTENT_NODE_PARENT_ID, FIELD_CONTENT_NODE_TITLE,
				FIELD_CONTENT_NODE_CONTENT, FIELD_CONTENT_NODE_AUTHOR };
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query(TABLE_NAME_CONTENT_NODE, columns,
				FIELD_CONTENT_NODE_PUB_ID + "=? AND "
						+ FIELD_CONTENT_NODE_PARENT_ID + "=?", new String[] {
						String.valueOf(pubId), String.valueOf(parentId) },
				null, null, null);
		if (c.moveToFirst()) {
			do {
				ContentNode contentNode = new ContentNode();
				contentNode.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
				contentNode.setAuthor(c.getString(c
						.getColumnIndex(FIELD_CONTENT_NODE_AUTHOR)));
				contentNode.setContent(c.getString(c
						.getColumnIndex(FIELD_CONTENT_NODE_CONTENT)));
				contentNode.setParentId(c.getLong(c
						.getColumnIndex(FIELD_CONTENT_NODE_PARENT_ID)));
				contentNode.setPubId(c.getLong(c
						.getColumnIndex(FIELD_CONTENT_NODE_PUB_ID)));
				contentNode.setTitle(c.getString(c
						.getColumnIndex(FIELD_CONTENT_NODE_TITLE)));
				contentNodes.add(contentNode);
			} while (c.moveToNext());
		}
		db.close();
		return contentNodes;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#getContentNode
	 * (int)
	 */
	@Override
	public ContentNode getContentNode(long contentNodeId) {
		ContentNode contentNode = null;
		String[] columns = new String[] { FIELD_ID, FIELD_CONTENT_NODE_PUB_ID,
				FIELD_CONTENT_NODE_PARENT_ID, FIELD_CONTENT_NODE_TITLE,
				FIELD_CONTENT_NODE_CONTENT, FIELD_CONTENT_NODE_AUTHOR };
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query(TABLE_NAME_CONTENT_NODE, columns, FIELD_ID + "=?",
				new String[] { String.valueOf(contentNodeId) }, null, null,
				null);
		if (c.moveToFirst()) {
			contentNode = new ContentNode();
			contentNode.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
			contentNode.setAuthor(c.getString(c
					.getColumnIndex(FIELD_CONTENT_NODE_AUTHOR)));
			contentNode.setContent(c.getString(c
					.getColumnIndex(FIELD_CONTENT_NODE_CONTENT)));
			contentNode.setParentId(c.getLong(c
					.getColumnIndex(FIELD_CONTENT_NODE_PARENT_ID)));
			contentNode.setPubId(c.getLong(c
					.getColumnIndex(FIELD_CONTENT_NODE_PUB_ID)));
			contentNode.setTitle(c.getString(c
					.getColumnIndex(FIELD_CONTENT_NODE_TITLE)));
		}
		db.close();
		return contentNode;
	}

	private ContentValues createPubAttachmentContentValues(
			PubAttachment pubAttachment) {
		ContentValues cv = new ContentValues();
		cv.put(FIELD_ATTACHMENT_CONTENT_NODE_ID,
				pubAttachment.getContentNodeId());
		cv.put(FIELD_ATTACHMENT_CID, pubAttachment.getName());
		cv.put(FIELD_ATTACHMENT_TYPE, pubAttachment.getContentType());
		cv.put(FIELD_ATTACHMENT_FILEPATH, pubAttachment.getFilepath());
		cv.put(FIELD_ATTACHMENT_PUB_ID, pubAttachment.getPubId());
		return cv;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#insertPubAttachment
	 * (com.aspire.android.heliar.pub.PubAttachment)
	 */
	@Override
	public long insertPubAttachment(PubAttachment attachment) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = createPubAttachmentContentValues(attachment);
		long id = db.insert(TABLE_NAME_ATTACHMENT, null, cv);
		db.close();
		return id;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#updatePubAttachment
	 * (int, com.aspire.android.heliar.pub.PubAttachment)
	 */
	@Override
	public void updatePubAttachment(long id, PubAttachment attachment) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Long.toString(id) };
		ContentValues cv = createPubAttachmentContentValues(attachment);
		db.update(TABLE_NAME_ATTACHMENT, cv, where, whereValue);
		db.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#deletePubAttachment
	 * (int)
	 */
	@Override
	public void deletePubAttachment(long id) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Long.toString(id) };
		db.delete(TABLE_NAME_ATTACHMENT, where, whereValue);
		db.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#list(int)
	 */
	@Override
	public List<PubAttachment> listPubAttachments(long contentNodeId) {

		List<PubAttachment> pubAttachments = new LinkedList<PubAttachment>();

		String[] columns = new String[] { FIELD_ID,
				FIELD_ATTACHMENT_CONTENT_NODE_ID, FIELD_ATTACHMENT_CID,
				FIELD_ATTACHMENT_TYPE, FIELD_ATTACHMENT_FILEPATH,
				FIELD_ATTACHMENT_PUB_ID };
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor c = db.query(TABLE_NAME_ATTACHMENT, columns,
				FIELD_ATTACHMENT_CONTENT_NODE_ID + "=?",
				new String[] { String.valueOf(contentNodeId) }, null, null,
				null);

		if (c.moveToFirst()) {
			do {
				PubAttachment pubAttachment = null;
				String contentType = c.getString(c
						.getColumnIndex(FIELD_ATTACHMENT_TYPE));
				if (contentType.equals(PubAttachment.CONTENT_TYPE_IMAGE_GIF)
						|| contentType
								.equals(PubAttachment.CONTENT_TYPE_IMAGE_JPEG)) {
					pubAttachment = new ImageAttachment();
					pubAttachment.setContentType(contentType);
					pubAttachment.setContentNodeId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_CONTENT_NODE_ID)));
					pubAttachment.setFilepath(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_FILEPATH)));
					pubAttachment.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
					pubAttachment.setName(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_CID)));
					pubAttachment.setPubId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_PUB_ID)));
					pubAttachments.add(pubAttachment);

				} else if (contentType
						.equals(PubAttachment.CONTENT_TYPE_SOUND_AMR)
						|| contentType
								.equals(PubAttachment.CONTENT_TYPE_SOUND_MIDI)) {
					pubAttachment = new SoundAttachment();
					pubAttachment.setContentType(contentType);
					pubAttachment.setContentNodeId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_CONTENT_NODE_ID)));
					pubAttachment.setFilepath(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_FILEPATH)));
					pubAttachment.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
					pubAttachment.setName(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_CID)));
					pubAttachment.setPubId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_PUB_ID)));

					pubAttachments.add(pubAttachment);

				}
			} while (c.moveToNext());

		}
		db.close();
		return pubAttachments;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.aspire.android.heliar.pub.storage.PubStorageManager#getPubAttachment
	 * (int)
	 */
	@Override
	public PubAttachment getPubAttachment(long id) {
		PubAttachment pubAttachment = null;

		String[] columns = new String[] { FIELD_ID,
				FIELD_ATTACHMENT_CONTENT_NODE_ID, FIELD_ATTACHMENT_CID,
				FIELD_ATTACHMENT_TYPE, FIELD_ATTACHMENT_FILEPATH,
				FIELD_ATTACHMENT_PUB_ID };

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query(TABLE_NAME_ATTACHMENT, columns, FIELD_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null);
		if (c.moveToFirst()) {
			String contentType = c.getString(c
					.getColumnIndex(FIELD_ATTACHMENT_TYPE));
			if (contentType.equals(PubAttachment.CONTENT_TYPE_IMAGE_JPEG)
					|| contentType.equals(PubAttachment.CONTENT_TYPE_IMAGE_GIF)) {
				pubAttachment = new ImageAttachment();
				pubAttachment.setContentType(contentType);
				pubAttachment.setContentNodeId(c.getLong(c
						.getColumnIndex(FIELD_ATTACHMENT_CONTENT_NODE_ID)));
				pubAttachment.setFilepath(c.getString(c
						.getColumnIndex(FIELD_ATTACHMENT_FILEPATH)));
				pubAttachment.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
				pubAttachment.setName(c.getString(c
						.getColumnIndex(FIELD_ATTACHMENT_CID)));
				pubAttachment.setPubId(c.getLong(c
						.getColumnIndex(FIELD_ATTACHMENT_PUB_ID)));

			} else if (contentType.equals(PubAttachment.CONTENT_TYPE_SOUND_AMR)
					|| contentType
							.equals(PubAttachment.CONTENT_TYPE_SOUND_MIDI)) {
				pubAttachment = new SoundAttachment();
				pubAttachment.setContentType(contentType);
				pubAttachment.setContentNodeId(c.getLong(c
						.getColumnIndex(FIELD_ATTACHMENT_CONTENT_NODE_ID)));
				pubAttachment.setFilepath(c.getString(c
						.getColumnIndex(FIELD_ATTACHMENT_FILEPATH)));
				pubAttachment.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
				pubAttachment.setName(c.getString(c
						.getColumnIndex(FIELD_ATTACHMENT_CID)));
				pubAttachment.setPubId(c.getLong(c
						.getColumnIndex(FIELD_ATTACHMENT_PUB_ID)));

			}
		}
		db.close();
		return pubAttachment;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#
	 * deleteContentNodesByPubId(long)
	 */
	@Override
	public void deleteContentNodesByPubId(long pubId) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_CONTENT_NODE_PUB_ID + "=?";
		String[] whereValue = { Long.toString(pubId) };
		db.delete(TABLE_NAME_CONTENT_NODE, where, whereValue);
		db.close();

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#
	 * deletetPubAttachmentsByPubId(long)
	 */
	@Override
	public void deletetPubAttachmentsByPubId(long pubId) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ATTACHMENT_PUB_ID + "=?";
		String[] whereValue = { Long.toString(pubId) };
		db.delete(TABLE_NAME_ATTACHMENT, where, whereValue);
		db.close();

	}

	/* (non-Javadoc)
	 * @see com.aspire.android.heliar.pub.storage.PubStorageManager#listPubAttachmentsByPubId(long)
	 */
	@Override
	public List<PubAttachment> listPubAttachmentsByPubId(long pubId) {
		List<PubAttachment> pubAttachments = new LinkedList<PubAttachment>();

		String[] columns = new String[] { FIELD_ID,
				FIELD_ATTACHMENT_CONTENT_NODE_ID, FIELD_ATTACHMENT_CID,
				FIELD_ATTACHMENT_TYPE, FIELD_ATTACHMENT_FILEPATH,
				FIELD_ATTACHMENT_PUB_ID };
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor c = db.query(TABLE_NAME_ATTACHMENT, columns,
				FIELD_ATTACHMENT_PUB_ID + "=?",
				new String[] { Long.toString(pubId) }, null, null,
				null);

		if (c.moveToFirst()) {
			do {
				PubAttachment pubAttachment = null;
				String contentType = c.getString(c
						.getColumnIndex(FIELD_ATTACHMENT_TYPE));
				if (contentType.equals(PubAttachment.CONTENT_TYPE_IMAGE_GIF)
						|| contentType
								.equals(PubAttachment.CONTENT_TYPE_IMAGE_JPEG)) {
					pubAttachment = new ImageAttachment();
					pubAttachment.setContentType(contentType);
					pubAttachment.setContentNodeId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_CONTENT_NODE_ID)));
					pubAttachment.setFilepath(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_FILEPATH)));
					pubAttachment.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
					pubAttachment.setName(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_CID)));
					pubAttachment.setPubId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_PUB_ID)));
					pubAttachments.add(pubAttachment);

				} else if (contentType
						.equals(PubAttachment.CONTENT_TYPE_SOUND_AMR)
						|| contentType
								.equals(PubAttachment.CONTENT_TYPE_SOUND_MIDI)) {
					pubAttachment = new SoundAttachment();
					pubAttachment.setContentType(contentType);
					pubAttachment.setContentNodeId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_CONTENT_NODE_ID)));
					pubAttachment.setFilepath(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_FILEPATH)));
					pubAttachment.setId(c.getLong(c.getColumnIndex(FIELD_ID)));
					pubAttachment.setName(c.getString(c
							.getColumnIndex(FIELD_ATTACHMENT_CID)));
					pubAttachment.setPubId(c.getLong(c
							.getColumnIndex(FIELD_ATTACHMENT_PUB_ID)));

					pubAttachments.add(pubAttachment);

				}
			} while (c.moveToNext());

		}
		db.close();
		return pubAttachments;
	}

}
